using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
namespace WPFExcelReport
{
    public partial class VICC_QPSTEP
    {
        public DataRow Convert2ICC_QPSTEP(DataRow dwInput, string ITXDBVERSION, bool IsConvert = false)
        {
            DataSet SResult = new DataSet();
            SResult = QueryICC_QPSTEP(dwInput, 0);
            DataRow dwReturn = null;
            if (SResult.Tables[0].Rows.Count > 0)
            {
                dwReturn = SResult.Tables[0].Rows[0];
            }
            else
            {
                dwReturn = SResult.Tables[0].NewRow();
            }
            switch (ITXDBVERSION)
            {
                case "YGR":
                    #region=========
         dwReturn["QPMKURZB"] = " ".PadRight(20 ); 
         dwReturn["QPMMEEH"] = " ".PadRight(3 ); 
         dwReturn["QPMBEZ"] = " ".PadRight(50 ); 
         dwReturn["WAPBEM"] = " ".PadRight(20 ); 
         dwReturn["WAPBIS"] = " ".PadRight(4 ); 
         dwReturn["WAPME"] = " ".PadRight(3 ); 
         dwReturn["WAPMIN"] = " ".PadRight(0 ); 
         dwReturn["WAPMAX"] = " ".PadRight(0 ); 
         dwReturn["QPSMANDANT"] = " ".PadRight(2 ); 
         dwReturn["QPSPNRKRS"] = " ".PadRight(2 ); 
         dwReturn["QPSPLFDNR"] = " ".PadRight(6 ); 
         dwReturn["QPSSTEP"] = " ".PadRight(4 ); 
         dwReturn["QPSAENZL"] = " ".PadRight(0 ); 
         dwReturn["QPSAENB"] = " ".PadRight(8 ); 
         dwReturn["QPSAEND"] = " ".PadRight(0 ); 
         dwReturn["QPSNEUB"] = " ".PadRight(8 ); 
         dwReturn["QPSNEUD"] = " ".PadRight(0 ); 
         dwReturn["QPSPRFGRP"] = " ".PadRight(1 ); 
         dwReturn["QPSARTFB"] = " ".PadRight(1 ); 
         dwReturn["QPSAGANG"] = " ".PadRight(15 ); 
         dwReturn["QPSGENKZ"] = " ".PadRight(1 ); 
         dwReturn["QPSMERKM"] = " ".PadRight(4 ); 
         dwReturn["QPSPMGRP"] = " ".PadRight(8 ); 
         dwReturn["QPSDYNTAB"] = " ".PadRight(2 ); 
         dwReturn["QPSPGROE"] = " ".PadRight(2 ); 
         dwReturn["QPSFEHLANT"] = " ".PadRight(0 ); 
         dwReturn["QPSMESSANZ"] = " ".PadRight(2 ); 
         dwReturn["QPSFORMEL"] = " ".PadRight(2 ); 
         dwReturn["QPSERFART"] = " ".PadRight(1 ); 
         dwReturn["QPSZEIT"] = " ".PadRight(0 ); 
         dwReturn["QPSMENGE"] = " ".PadRight(0 ); 
         dwReturn["QPSKSTKRS"] = " ".PadRight(2 ); 
         dwReturn["QPSKST"] = " ".PadRight(4 ); 
         dwReturn["QPSRAGANG"] = " ".PadRight(15 ); 
         dwReturn["QPSSPGRD"] = " ".PadRight(2 ); 
         dwReturn["QPSNSWERT"] = " ".PadRight(0 ); 
         dwReturn["QPSNSUG"] = " ".PadRight(0 ); 
         dwReturn["QPSNSOG"] = " ".PadRight(0 ); 
         dwReturn["QPSNAUS"] = " ".PadRight(4 ); 
         dwReturn["QPSASWERT"] = " ".PadRight(10 ); 
         dwReturn["QPSASUG"] = " ".PadRight(10 ); 
         dwReturn["QPSASOG"] = " ".PadRight(10 ); 
         dwReturn["QPSAAUS"] = " ".PadRight(4 ); 
         dwReturn["QPSNEUG"] = " ".PadRight(0 ); 
         dwReturn["QPSNEOG"] = " ".PadRight(0 ); 
         dwReturn["QPSDRUCKKZ"] = " ".PadRight(1 ); 
         dwReturn["QPSUGKZ"] = " ".PadRight(1 ); 
         dwReturn["QPSOGKZ"] = " ".PadRight(1 ); 
         dwReturn["QPSCWEIGHT"] = " ".PadRight(0 ); 
         dwReturn["QPSFPROCCO"] = " ".PadRight(0 ); 
         dwReturn["QPSWE"] = " ".PadRight(3 ); 
         dwReturn["QPSPKSTKRS"] = " ".PadRight(2 ); 
         dwReturn["QPSPKST"] = " ".PadRight(4 ); 
         dwReturn["QPSFILLER"] = " ".PadRight(94 ); 
                    #endregion
                    break;
                case "JLT":

                    break;
                default:

                    break;
            }
            if (IsConvert)
            {
                foreach (DataColumn cm in dwInput.Table.Columns)
                {
                    if (dwInput[cm.ColumnName].ToString() == "")
                        continue;
                    if (dwReturn.Table.Columns.Contains(cm.ColumnName))
                    {
                        dwReturn[cm.ColumnName] = dwInput[cm.ColumnName];
                    }
                }
            }
            return dwReturn;
        }
        public void MapICC_QPSTEP(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveICC_QPSTEP(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            setResult = QueryICC_QPSTEP(dwData);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }
            MapICC_QPSTEP(ref dwLocal, dwData);

            if (!flgExist)
            {

                InsertICC_QPSTEP(dwLocal, ITXDBVERSION);
            }
            UpdateICC_QPSTEP(dwLocal);
        }
        public void InsertICC_QPSTEP(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (ITXDBVERSION)
            {
                case "YGR":
                    or1.CommandText = "INSERT INTO ICC_QPSTEP(VAKBMANDANT, VAKBKOLL, VAKBART, VAKBKUNDNR, VAKBFARBE, VAKBKDARTNR, VAKBKDARTBEZ, VAKBKDFARBNR, VAKBANDRUCK, VAKBMEGEW, VAKBMENGEH, VAKBGEWVON, VAKBGEWBIS, VAKBGEWCA, VAKBGEWFKT, VAKBGEWGRP, VAKBMEGFKT, VAKBMEGGRP, VAKBAENDZL, VAKBAENDBEN, VAKBAENDDAT, VAKBNEUBEN, VAKBNEUDAT, VAKBFILLER)"
                        + " VALUES (:VAKBMANDANT, :VAKBKOLL, :VAKBART, :VAKBKUNDNR, :VAKBFARBE, '                         ', '                         ', '                             ', ' ', '   ', '   ', 0, 0, ' ', '000000000000', ' ', '000000000000', ' ', 0, '        ', '000000', '        ', '      ', '                                          ')";
                    or1.Parameters.Add("VAKBMANDANT", OracleType.NVarChar).Value = dwData["VAKBMANDANT"].ToString();
                    or1.Parameters.Add("VAKBKOLL", OracleType.NVarChar).Value = dwData["VAKBKOLL"].ToString();
                    or1.Parameters.Add("VAKBART", OracleType.NVarChar).Value = dwData["VAKBART"].ToString();
                    or1.Parameters.Add("VAKBFARBE", OracleType.NVarChar).Value = dwData["VAKBFARBE"].ToString();
                    or1.Parameters.Add("VAKBKUNDNR", OracleType.NVarChar).Value = dwData["VAKBKUNDNR"].ToString();
                    break;
                //VAFMANDANT VAFKOLL VAFART VAFFARBE VAFBEZ VAFFFARBE

            }
            
            Connect2Proxy(or1);
        }
        public DataSet QueryICC_QPSTEP(DataRow dwData, int option = 1)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0:
                    or1.CommandText = "SELECT * FROM ICC_QPSTEP WHERE  ROWNUM<2 ";
                    break;
              
                case 99:
                    or1.CommandText = "SELECT * FROM ICC_QPSTEP  WHERE ROWNUM<50";
                    break;
                default:
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
                Result.Tables[0].TableName = "ICC_QPSTEP";
            Return = Result;
            return Return;
        }
        public void DeleteICC_QPSTEP(DataRow dwData, int option = 1)
        {

            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM ICC_QPSTEP   WHERE  VAKBMANDANT=:VAKBMANDANT AND VAKBKOLL=:VAKBKOLL AND VAKBART =:VAKBART AND VAKBFARBE=:VAKBFARBE AND VAKBKUNDNR=:VAKBKUNDNR";
                     or1.Parameters.Add("VAKBMANDANT", OracleType.NVarChar).Value = dwData["VAKBMANDANT"].ToString();
                    or1.Parameters.Add("VAKBKOLL", OracleType.NVarChar).Value = dwData["VAKBKOLL"].ToString();
                    or1.Parameters.Add("VAKBART", OracleType.NVarChar).Value = dwData["VAKBART"].ToString();
                    or1.Parameters.Add("VAKBFARBE", OracleType.NVarChar).Value = dwData["VAKBFARBE"].ToString();
                    or1.Parameters.Add("VAKBKUNDNR", OracleType.NVarChar).Value = dwData["VAKBKUNDNR"].ToString();
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        } 
      
        public void UpdateICC_QPSTEP(DataRow dwData, string ITXDBVERSION = "50.24", int option = 1)
        {
            OracleCommand or1;
            DataSet Result = new DataSet();
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    #region====Command==== 
                    or1.CommandText = "UPDATE ICC_QPSTEP SET "
                    +"QPMKURZB= :QPMKURZB,"
                    +"QPMMEEH= :QPMMEEH,"
                    +"QPMBEZ= :QPMBEZ,"
                    +"WAPBEM= :WAPBEM,"
                    +"WAPBIS= :WAPBIS,"
                    +"WAPME= :WAPME,"
                    +"WAPMIN= :WAPMIN,"
                    +"WAPMAX= :WAPMAX,"
                    +"QPSMANDANT= :QPSMANDANT,"
                    +"QPSPNRKRS= :QPSPNRKRS,"
                    +"QPSPLFDNR= :QPSPLFDNR,"
                    +"QPSSTEP= :QPSSTEP,"
                    +"QPSAENZL= :QPSAENZL,"
                    +"QPSAENB= :QPSAENB,"
                    +"QPSAEND= :QPSAEND,"
                    +"QPSNEUB= :QPSNEUB,"
                    +"QPSNEUD= :QPSNEUD,"
                    +"QPSPRFGRP= :QPSPRFGRP,"
                    +"QPSARTFB= :QPSARTFB,"
                    +"QPSAGANG= :QPSAGANG,"
                    +"QPSGENKZ= :QPSGENKZ,"
                    +"QPSMERKM= :QPSMERKM,"
                    +"QPSPMGRP= :QPSPMGRP,"
                    +"QPSDYNTAB= :QPSDYNTAB,"
                    +"QPSPGROE= :QPSPGROE,"
                    +"QPSFEHLANT= :QPSFEHLANT,"
                    +"QPSMESSANZ= :QPSMESSANZ,"
                    +"QPSFORMEL= :QPSFORMEL,"
                    +"QPSERFART= :QPSERFART,"
                    +"QPSZEIT= :QPSZEIT,"
                    +"QPSMENGE= :QPSMENGE,"
                    +"QPSKSTKRS= :QPSKSTKRS,"
                    +"QPSKST= :QPSKST,"
                    +"QPSRAGANG= :QPSRAGANG,"
                    +"QPSSPGRD= :QPSSPGRD,"
                    +"QPSNSWERT= :QPSNSWERT,"
                    +"QPSNSUG= :QPSNSUG,"
                    +"QPSNSOG= :QPSNSOG,"
                    +"QPSNAUS= :QPSNAUS,"
                    +"QPSASWERT= :QPSASWERT,"
                    +"QPSASUG= :QPSASUG,"
                    +"QPSASOG= :QPSASOG,"
                    +"QPSAAUS= :QPSAAUS,"
                    +"QPSNEUG= :QPSNEUG,"
                    +"QPSNEOG= :QPSNEOG,"
                    +"QPSDRUCKKZ= :QPSDRUCKKZ,"
                    +"QPSUGKZ= :QPSUGKZ,"
                    +"QPSOGKZ= :QPSOGKZ,"
                    +"QPSCWEIGHT= :QPSCWEIGHT,"
                    +"QPSFPROCCO= :QPSFPROCCO,"
                    +"QPSWE= :QPSWE,"
                    +"QPSPKSTKRS= :QPSPKSTKRS,"
                    +"QPSPKST= :QPSPKST,"
                    +"QPSFILLER= :QPSFILLER,"
                        + " WHERE  "
                        + "  VAKBMANDANT=:VAKBMANDANT AND VAKBKOLL=:VAKBKOLL AND VAKBART =:VAKBART AND VAKBFARBE=:VAKBFARBE AND VAKBKUNDNR=:VAKBKUNDNR";
                    #endregion

                    #region====Parameters====
          or1.Parameters.Add("QPMKURZB", OracleType.NVarChar).Value = dwData["QPMKURZB"].ToString(); 
          or1.Parameters.Add("QPMMEEH", OracleType.NVarChar).Value = dwData["QPMMEEH"].ToString(); 
          or1.Parameters.Add("QPMBEZ", OracleType.NVarChar).Value = dwData["QPMBEZ"].ToString(); 
          or1.Parameters.Add("WAPBEM", OracleType.NVarChar).Value = dwData["WAPBEM"].ToString(); 
          or1.Parameters.Add("WAPBIS", OracleType.NVarChar).Value = dwData["WAPBIS"].ToString(); 
          or1.Parameters.Add("WAPME", OracleType.NVarChar).Value = dwData["WAPME"].ToString(); 
          or1.Parameters.Add("WAPMIN", OracleType.NVarChar).Value = dwData["WAPMIN"].ToString(); 
          or1.Parameters.Add("WAPMAX", OracleType.NVarChar).Value = dwData["WAPMAX"].ToString(); 
          or1.Parameters.Add("QPSMANDANT", OracleType.NVarChar).Value = dwData["QPSMANDANT"].ToString(); 
          or1.Parameters.Add("QPSPNRKRS", OracleType.NVarChar).Value = dwData["QPSPNRKRS"].ToString(); 
          or1.Parameters.Add("QPSPLFDNR", OracleType.NVarChar).Value = dwData["QPSPLFDNR"].ToString(); 
          or1.Parameters.Add("QPSSTEP", OracleType.NVarChar).Value = dwData["QPSSTEP"].ToString(); 
          or1.Parameters.Add("QPSAENZL", OracleType.NVarChar).Value = dwData["QPSAENZL"].ToString(); 
          or1.Parameters.Add("QPSAENB", OracleType.NVarChar).Value = dwData["QPSAENB"].ToString(); 
          or1.Parameters.Add("QPSAEND", OracleType.NVarChar).Value = dwData["QPSAEND"].ToString(); 
          or1.Parameters.Add("QPSNEUB", OracleType.NVarChar).Value = dwData["QPSNEUB"].ToString(); 
          or1.Parameters.Add("QPSNEUD", OracleType.NVarChar).Value = dwData["QPSNEUD"].ToString(); 
          or1.Parameters.Add("QPSPRFGRP", OracleType.NVarChar).Value = dwData["QPSPRFGRP"].ToString(); 
          or1.Parameters.Add("QPSARTFB", OracleType.NVarChar).Value = dwData["QPSARTFB"].ToString(); 
          or1.Parameters.Add("QPSAGANG", OracleType.NVarChar).Value = dwData["QPSAGANG"].ToString(); 
          or1.Parameters.Add("QPSGENKZ", OracleType.NVarChar).Value = dwData["QPSGENKZ"].ToString(); 
          or1.Parameters.Add("QPSMERKM", OracleType.NVarChar).Value = dwData["QPSMERKM"].ToString(); 
          or1.Parameters.Add("QPSPMGRP", OracleType.NVarChar).Value = dwData["QPSPMGRP"].ToString(); 
          or1.Parameters.Add("QPSDYNTAB", OracleType.NVarChar).Value = dwData["QPSDYNTAB"].ToString(); 
          or1.Parameters.Add("QPSPGROE", OracleType.NVarChar).Value = dwData["QPSPGROE"].ToString(); 
          or1.Parameters.Add("QPSFEHLANT", OracleType.NVarChar).Value = dwData["QPSFEHLANT"].ToString(); 
          or1.Parameters.Add("QPSMESSANZ", OracleType.NVarChar).Value = dwData["QPSMESSANZ"].ToString(); 
          or1.Parameters.Add("QPSFORMEL", OracleType.NVarChar).Value = dwData["QPSFORMEL"].ToString(); 
          or1.Parameters.Add("QPSERFART", OracleType.NVarChar).Value = dwData["QPSERFART"].ToString(); 
          or1.Parameters.Add("QPSZEIT", OracleType.NVarChar).Value = dwData["QPSZEIT"].ToString(); 
          or1.Parameters.Add("QPSMENGE", OracleType.NVarChar).Value = dwData["QPSMENGE"].ToString(); 
          or1.Parameters.Add("QPSKSTKRS", OracleType.NVarChar).Value = dwData["QPSKSTKRS"].ToString(); 
          or1.Parameters.Add("QPSKST", OracleType.NVarChar).Value = dwData["QPSKST"].ToString(); 
          or1.Parameters.Add("QPSRAGANG", OracleType.NVarChar).Value = dwData["QPSRAGANG"].ToString(); 
          or1.Parameters.Add("QPSSPGRD", OracleType.NVarChar).Value = dwData["QPSSPGRD"].ToString(); 
          or1.Parameters.Add("QPSNSWERT", OracleType.NVarChar).Value = dwData["QPSNSWERT"].ToString(); 
          or1.Parameters.Add("QPSNSUG", OracleType.NVarChar).Value = dwData["QPSNSUG"].ToString(); 
          or1.Parameters.Add("QPSNSOG", OracleType.NVarChar).Value = dwData["QPSNSOG"].ToString(); 
          or1.Parameters.Add("QPSNAUS", OracleType.NVarChar).Value = dwData["QPSNAUS"].ToString(); 
          or1.Parameters.Add("QPSASWERT", OracleType.NVarChar).Value = dwData["QPSASWERT"].ToString(); 
          or1.Parameters.Add("QPSASUG", OracleType.NVarChar).Value = dwData["QPSASUG"].ToString(); 
          or1.Parameters.Add("QPSASOG", OracleType.NVarChar).Value = dwData["QPSASOG"].ToString(); 
          or1.Parameters.Add("QPSAAUS", OracleType.NVarChar).Value = dwData["QPSAAUS"].ToString(); 
          or1.Parameters.Add("QPSNEUG", OracleType.NVarChar).Value = dwData["QPSNEUG"].ToString(); 
          or1.Parameters.Add("QPSNEOG", OracleType.NVarChar).Value = dwData["QPSNEOG"].ToString(); 
          or1.Parameters.Add("QPSDRUCKKZ", OracleType.NVarChar).Value = dwData["QPSDRUCKKZ"].ToString(); 
          or1.Parameters.Add("QPSUGKZ", OracleType.NVarChar).Value = dwData["QPSUGKZ"].ToString(); 
          or1.Parameters.Add("QPSOGKZ", OracleType.NVarChar).Value = dwData["QPSOGKZ"].ToString(); 
          or1.Parameters.Add("QPSCWEIGHT", OracleType.NVarChar).Value = dwData["QPSCWEIGHT"].ToString(); 
          or1.Parameters.Add("QPSFPROCCO", OracleType.NVarChar).Value = dwData["QPSFPROCCO"].ToString(); 
          or1.Parameters.Add("QPSWE", OracleType.NVarChar).Value = dwData["QPSWE"].ToString(); 
          or1.Parameters.Add("QPSPKSTKRS", OracleType.NVarChar).Value = dwData["QPSPKSTKRS"].ToString(); 
          or1.Parameters.Add("QPSPKST", OracleType.NVarChar).Value = dwData["QPSPKST"].ToString(); 
          or1.Parameters.Add("QPSFILLER", OracleType.NVarChar).Value = dwData["QPSFILLER"].ToString(); 
                    #endregion
                    break; 
                default:
                    break;
            }


            Connect2Proxy(or1);
        }
    }
}
